package cn.zhl.different;

import com.alibaba.excel.EasyExcel;
import org.junit.Test;

import java.io.BufferedReader;
import java.io.FileReader;
import java.util.*;

/**
 * @program: demos
 * @description: 找数据库和excel中字段的不同
 * @author: 刘振华
 * @create: 2020-11-06 16:46
 **/
public class FindDifferentBetweenDbAndExcel {

	@Test
	public void tmp(){
		System.out.println(System.currentTimeMillis());
	}
	public static Map<String, Set<String>> map = new HashMap<>(100);
	@Test
	public void printDiff()throws Exception{
		String fileName = "D:\\work\\文档\\3.塔里木\\设计\\设备数据管理与应用项目模型设计(Oracle).xlsx";
		// 读取全部sheet
		// 这里需要注意 DemoDataListener的doAfterAllAnalysed 会在每个sheet读取完毕后调用一次。然后所有sheet都会往同一个DemoDataListener里面写
		EasyExcel.read(fileName, DifferentEo.class, new DifferentExcelListener()).doReadAll();

		Map<String,Set<String>> dbMap = new HashMap<>();

		String dbFile = "D:\\work\\文档\\3.塔里木\\数据库\\sql\\zy-traim.sql";
		BufferedReader bfr = new BufferedReader(new FileReader(dbFile));
		String line = "";
		boolean flag = false;
		String tabName = "";
		while ((line = bfr.readLine())!=null){
			if(line.startsWith("CREATE TABLE")){
				tabName = line.split("\"")[3];
				Set<String> dbSet = new HashSet<>();
				dbMap.put(tabName,dbSet);
				flag = true;
				continue;
			}
			if(flag){
				if(")".equals(line)){
					tabName = "";
					flag = false;
					continue;
				}
				String colName = line.split("\"")[1];
				dbMap.get(tabName).add(colName);
			}
		}

		Map<String, String> noColumnMap = new HashMap<>();
		final Set<String> nonTable = new HashSet<>();
		map.forEach((k,v)->{
			Set<String> dbSet = dbMap.get(k);
			if(null == dbSet){
				nonTable.add(k);
				return;
			}
			v.forEach(ecol->{
				if(!dbSet.contains(ecol)){
//					System.out.println(String.format("%s 表的 %s 字段在数据库中没有",k,ecol));
					String s = Optional.ofNullable(noColumnMap.get(k)).orElse("") + ",";
					noColumnMap.put(k,s+ecol);
				}
			});
		});
		bfr.close();
		noColumnMap.forEach((k,v)->{
			System.out.println(k+":["+v.substring(1)+"]");
		});
		System.out.println("===========以下表在数据库中没有=====================");
		nonTable.forEach(System.out::println);

	}
}
